ASP.NET Core Blazor | Data from two tables in DataGrid

how to display data from two database tables in blazor datagrid

In this video we will discuss how to retrieve and display data from two or more related tables in a DatGrid.

Database table relationships

The following are the different types of relationships between database tables.

sql database table relationships

One to One
One to Many
Many to Many

Consider the following 3 tables - Employees, Salaries and Departments

sql one to one relationship example

We have a one-to-one relationship between Employees and Salaries and Employees and Departments tables.
DeptId in Employees table is a foreign key referencing Id column in Departments table
SalaryId in Employees table is a foreign key referencing Id column in Salaries table

Displaying foreign key data in datagrid

We wan to display DepartmentName in the DataGrid along with employee firstname, lastname etc.
DepartmentName is present in the Departments table and it is linked to the Employees table through the foreign key DeptId.
So to display DepartmentName in the DataGrid we use <GridForeignColumn> component.

<GridForeignColumn ForeignDataSource="@Departments" HeaderText="Dept Name" 
                   Field=@nameof(Employee.DepartmentId)
                   ForeignKeyValue="DepartmentName">
</GridForeignColumn>

ForeignDataSource - Defines the foreign data.
Field - Defines the mapping column name to the foreign data.
ForeignKeyValue - Defines the display field from the foreign data.

`Complete Example (All CRUD operations supported)`

@page "/"
@*@page "/datagridfk"*@
@using Syncfusion.Blazor.Grids
@using Syncfusion.Blazor.DropDowns

<SfGrid @ref="employeeGrid" DataSource="@Employees"
        Toolbar="@(new List<string>() { "Add", "Edit", "Update", "Delete", "Cancel" })">
    <GridEditSettings AllowAdding="true" AllowEditing="true" AllowDeleting="true"></GridEditSettings>
    <GridEvents OnActionBegin="ActionBeginHandler" TValue="Employee"></GridEvents>
    <GridColumns>
        <GridColumn Field=@nameof(Employee.EmployeeId) IsPrimaryKey="true" HeaderText="ID" Width="60"></GridColumn>
        <GridColumn Field=@nameof(Employee.FirstName) HeaderText="First Name"></GridColumn>
        <GridColumn Field=@nameof(Employee.LastName) HeaderText="Last Name"></GridColumn>
        <GridColumn Field=@nameof(Employee.DateOfBrith) HeaderText="Date of Birth"></GridColumn>
        <GridColumn Field=@nameof(Employee.Email) HeaderText="Email"></GridColumn>
        <GridColumn Field=@nameof(Employee.Gender) HeaderText="Gender" Width="140px">
            <EditTemplate>
                <SfDropDownList ID="Gender" TItem="string" TValue="Gender"
                                @bind-Value="@((context as Employee).Gender)"
                                DataSource="@(Enum.GetNames(typeof(Gender)))">
                </SfDropDownList>
            </EditTemplate>
        </GridColumn>
        <GridForeignColumn Field=@nameof(Employee.DepartmentId)
                           HeaderText="Dept Name" Width="150"
                           ForeignKeyValue="DepartmentName" ForeignDataSource="@Departments">
        </GridForeignColumn>
    </GridColumns>
</SfGrid>

@code{

    public List<Employee> Employees { get; set; }

    public List<Department> Departments { get; set; }

    public SfGrid<Employee> employeeGrid { get; set; }

    [Inject]
    public IEmployeeService EmployeeService { get; set; }

    [Inject]
    public IDepartmentService DepartmentService { get; set; }

    protected override async Task OnInitializedAsync()
    {
        Employees = (await EmployeeService.GetAllEmployees()).ToList();
        Departments = (await DepartmentService.GetAllDepartments()).ToList();
    }

    public async void ActionBeginHandler(ActionEventArgs<Employee> Args)
    {
        if (Args.RequestType.Equals(Syncfusion.Blazor.Grids.Action.Save))
        {
            if (Args.Action == "Add")
            {
                await EmployeeService.AddEmployee(Args.Data);
                Employees = (await EmployeeService.GetAllEmployees()).ToList();
                employeeGrid.Refresh();
            }
            else
            {
                await EmployeeService.UpdateEmployee(Args.Data);
            }
        }
        if (Args.RequestType.Equals(Syncfusion.Blazor.Grids.Action.Delete))
        {
            await EmployeeService.DeleteEmployee(Args.Data.EmployeeId);
        }
    }
}